﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.IO;

namespace ExcelHelper
{
    public static class ExcelHandler //未完成
    {

        public static DataTable ReadExcel(string file, string sheetName = null, bool hasHeader=true)
        {
            DataTable dt = new DataTable();
            int startRow = 0;
            FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = null;
            ISheet sheet = null; 
            string fileExten = Path.GetExtension(file);
            if (fileExten == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (fileExten == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                throw new Exception("wrong file format");
            }
            if (sheetName == null)
            {
                sheet = workbook.GetSheetAt(0);
            }
            else
            {
                sheet = workbook.GetSheet(sheetName);
            }
            if (hasHeader) 
            {
                IRow header = sheet.GetRow(0); // 第一行
                int colNum = header.LastCellNum; // 总列数
                for (int i = header.FirstCellNum; i < colNum; i++)
                {
                    ICell cell = header.GetCell(i);
                    string cellValue = cell.StringCellValue;
                    if (cellValue != null)
                    {
                        DataColumn dtCol = new DataColumn(cellValue);
                        dt.Columns.Add(dtCol);
                    }
                }
                startRow = 1;
            }
            int rowNum = sheet.LastRowNum;
            for (int i = startRow; i <= rowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null) continue;
                DataRow dtRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dtRow[j] = row.GetCell(j).ToString();
                    }
                    dt.Rows.Add(dtRow);
                }
            }
            fs.Close();
            return dt;
        }
        //{
        //    DataTable dt = new DataTable();
        //    string fileExten = Path.GetExtension(file);
        //    if (fileExten == ".xls")
        //    {
        //        connStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", file);
        //    }
        //    else if (fileExten == ".xlsx")
        //    {
        //        connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", file);
        //    }
        //    else
        //    {
        //        throw new Exception("wrong file format");
        //    }

        //    OleDbConnection conn = new OleDbConnection(connStr);
        //    conn.Open();

        //    if (tableName == null) //如果没有选择tableName 默认选第一个
        //    {
        //        tableName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
        //    }

        //    OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format(@"Select * from {0}", tableName), conn);
        //    DataSet ds = new DataSet();
        //    adapter.Fill(ds);
        //    conn.Close();
        //    dt = ds.Tables[tableName];
        //    return dt;

        //}
    }
}
